﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace FindProductsByCategory
{
    public class Program
    {
        private static List<string> FindCategories(SqlConnection dbCon)
        {
            SqlCommand findCategories = new SqlCommand(
                    "SELECT DISTINCT CategoryName " +
                    "FROM Categories " +
                    "ORDER BY CategoryName", dbCon);
            SqlDataReader categoryReader = findCategories.ExecuteReader();
            List<string> categories = new List<string>();
            using (categoryReader)
            {
                while (categoryReader.Read())
                {
                    categories.Add((string)categoryReader["CategoryName"]);
                }
            }
            return categories;
        }

        private static Dictionary<string, List<string>> FindProductsByCategory(
            SqlConnection dbCon, List<string> categories)
        {
            var productsByCategory = new Dictionary<string, List<string>>();
            foreach (string category in categories)
            {
                SqlCommand findNames = new SqlCommand(
                        "SELECT ProductName " +
                        "FROM Products AS p " +
                        "JOIN Categories AS c ON p.CategoryID = c.CategoryID " +
                        "WHERE c.CategoryName = @category " +
                        "ORDER BY p.ProductName", dbCon);
                findNames.Parameters.Add(new SqlParameter("@category", category));

                SqlDataReader productReader = findNames.ExecuteReader();
                using (productReader)
                {
                    productsByCategory[category] = new List<string>();
                    while (productReader.Read())
                    {
                        string product = (string)productReader["ProductName"];
                        productsByCategory[category].Add(product);
                    }
                }
            }
            return productsByCategory;
        }

        private static void PrintProductsByCategory(
            Dictionary<string, List<string>> productsByCategory)
        {
            foreach (string category in productsByCategory.Keys)
            {
                Console.WriteLine("{0}: ", category);
                foreach (string product in productsByCategory[category])
                {
                    Console.WriteLine("\t{0}", product);
                }
            }
        }

        public static void Main()
        {
            List<string> categories;
            Dictionary<string, List<string>> productsByCategory;

            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
            "Database=Northwind; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                categories = FindCategories(dbCon);
                productsByCategory = FindProductsByCategory(dbCon, categories);
            }

            PrintProductsByCategory(productsByCategory);
        }
    }
}
